USE SGLDBMP01;
GO

-- SELECT * FROM dbo.[Ticket] (NoLock);
-- Ref: https://www.simple-talk.com/sql/database-administration/handling-errors-in-sql-server-2012/
--		http://stackoverflow.com/questions/9477502/get-the-last-inserted-row-id-with-sql-statement
--		http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

/* Unit Test Script:

Schema:
-------
dbo.[Ticket]
Column_name		Type		Length	Input
-----------------------------------------
TicketId		bigint		8		Auto
UserId			bigint		8		Manual
CategoryId		bigint		8		Manual
UserQuery		nvarchar	4000	Manual
DateCreated		datetime	8		Auto

dbo.[TicketStatus]
Column_name		Type		Length	Input
-----------------------------------------
TicketStatusId	bigint		8		Auto
TicketId		bigint		8		Manual
StatusCodeId	bigint		8		null
DateUpdated		datetime	8		Auto

SELECT * FROM dbo.[Ticket] (NoLock);
SELECT * FROM dbo.[TicketStatus] (NoLock);

OP Execute Query:
-----------------


Positive Condition:
-------------------


Negative Condition:
-------------------

*/


IF OBJECT_ID('dbo.sProc_INSERT_Ticket', 'P') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.sProc_INSERT_Ticket;
	PRINT 'Stored Proc with the name: ''sProc_INSERT_Ticket'' already exists, hence dropped';
END;
GO
 
CREATE PROCEDURE dbo.sProc_INSERT_Ticket
	@UserId	BIGINT
	, @CategoryId BIGINT
	, @UserQuery NVARCHAR(4000)
	, @StatusCodeId CHAR(1) = 'I'
AS
BEGIN

  BEGIN TRY
	BEGIN TRANSACTION;
	
		-- Insert to [ticket] table	
		INSERT INTO dbo.Ticket (UserId, CategoryId, UserQuery )
		VALUES (@UserId, @CategoryId, @UserQuery);

		-- Insert to [ticketStatus] table
		INSERT INTO dbo.TicketStatus (TicketId, StatusCodeId)
		VALUES (SCOPE_IDENTITY(), @StatusCodeId)

	COMMIT TRANSACTION;	
  END TRY
  BEGIN CATCH
 
	IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
 
    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
 
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

  END CATCH
END;
GO

IF OBJECT_ID(N'[dbo].[sProc_INSERT_Ticket]', N'P') IS NOT NULL
    PRINT 'Stored Procedure: ''dbo.sProc_INSERT_Ticket'', created successfully';
GO

/* OP: Stored Procedure: 'dbo.sProc_INSERT_Ticket', created successfully

*/